ALTER TABLE LvReo_MasterList
ADD [Type] VARCHAR(100) NULL
GO
ALTER PROCEDURE [dbo].[getLvreo_MasterListEx]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '', --Zip
    @showAllUnassigned bit = 0
)

AS

BEGIN

declare @percentT1 money
declare @percentAA1 money
set @percentT1 = (select Value from Config where [Key] = 'PercentT1')
set @percentAA1 = (select Value from Config where [Key] = 'PercentAA1')

        SELECT
			ua.Fname + ' ' + ua.LName as InvestorName,
			EMD,
			Reimb,
			Insur,
			Assmts,
			DateAccept,
			m.Apn,
			m.PropertyLocation,
			m.ZipCode,
			StartDate,
			p.Stories,
			p.ConstructYear,
			p.TotalSqft,			
			p.Bedrooms,
			p.Bathrooms,
			p.GarageSqft,
			p.Pool,
			p.Spa,
			cast(dbo.fnCastAsMoney(PurchasePrice) as decimal(18,0)) as PurchasePrice
			,ClosingCost = cast(round(dbo.fnCastAsMoney(PurchasePrice) * @percentT1 / 100, 0) as int)
			,cast(dbo.fnCastAsMoney(RehabCosts) as decimal(18,0)) as RehabCosts
			-- PurchasePrice + ClosingCost + RehabCosts
            ,PurchaseNoteTotal =
				cast(round(dbo.fnCastAsMoney(PurchasePrice) + dbo.fnCastAsMoney(RehabCosts) + dbo.fnCastAsMoney(PurchasePrice) * @percentT1 / 100, 0) as int)		
           -- PurchaseNoteTotal / TotalSqft
           ,PurchaseCostSqft =
				cast(round((dbo.fnCastAsMoney(PurchasePrice) + dbo.fnCastAsMoney(RehabCosts) + dbo.fnCastAsMoney(PurchasePrice) * @percentT1 / 100) / ISNULL(dbo.fnCastAsMoney(TotalSqft), 1), 2) as decimal(18, 2))
			,cast(dbo.fnCastAsMoney(EstimatedSale) as decimal(18,0)) as EstimatedSale
			-- EstimatedSale / TotalSqft
           ,SaleCostSqft =
				cast(round(dbo.fnCastAsMoney(EstimatedSale) / ISNULL(dbo.fnCastAsMoney(TotalSqft), 1), 2) as decimal(18, 2))
           -- (NetDemandToEscrow - PurchaseNoteTotal)*.15 + EstimatedSale * @percentAA1 / 100
           ,CostOfSale =
				cast(round(((dbo.fnCastAsMoney(EstimatedSale) - dbo.fnCastAsMoney(EstimatedSale) * @percentAA1 / 100) - 
				(dbo.fnCastAsMoney(PurchasePrice) + dbo.fnCastAsMoney(RehabCosts) + dbo.fnCastAsMoney(PurchasePrice) * @percentT1 / 100))*dbo.fnGetCostOfSalePercent(FeePercent)
				+
				dbo.fnCastAsMoney(EstimatedSale) * @percentAA1 / 100, 0) as int)
			,AA = ''
			-- EstimatedSale - EstimatedSale * @percentAA1%
			,NetDemandToEscrow =
				cast(round(dbo.fnCastAsMoney(EstimatedSale) - dbo.fnCastAsMoney(EstimatedSale) * @percentAA1 / 100, 0) as int)
			-- ([Net Demand to escrow] - PurchaseNoteTotal) * 0.8
			,NetProfit =
				cast(round(((dbo.fnCastAsMoney(EstimatedSale) - dbo.fnCastAsMoney(EstimatedSale) * @percentAA1 / 100)
					- (dbo.fnCastAsMoney(PurchasePrice) + dbo.fnCastAsMoney(RehabCosts) + dbo.fnCastAsMoney(PurchasePrice) * @percentT1 / 100)) * dbo.fnGetNetProfitPercent(FeePercent), 0) as int)
			-- NetProfit / PurchaseNoteTotal
			,PercentOfProfitVsPurchase =
				cast(cast(round(100*(((dbo.fnCastAsMoney(EstimatedSale) - dbo.fnCastAsMoney(EstimatedSale) * @percentAA1 / 100) - (dbo.fnCastAsMoney(PurchasePrice) + dbo.fnCastAsMoney(RehabCosts) + dbo.fnCastAsMoney(PurchasePrice) * @percentT1 / 100)) * .8 / (dbo.fnCastAsMoney(PurchasePrice) + dbo.fnCastAsMoney(RehabCosts) + dbo.fnCastAsMoney(PurchasePrice) * @percentT1 / 100)), 1) as decimal(18, 1)) as varchar) + '%'
			-- PurchaseNoteTotal / EstimatedSale
			,CostToARV =
				cast(cast(round(100*((dbo.fnCastAsMoney(PurchasePrice) + dbo.fnCastAsMoney(RehabCosts) + dbo.fnCastAsMoney(PurchasePrice) * @percentT1 / 100) / ISNULL(dbo.fnCastAsMoney(EstimatedSale), 1)), 1) as decimal(18, 1)) as varchar) + '%'
			,CloseDate
			,[# Days] = ''
			-- CloseDate - StartDate
			,TotalDays = datediff(DAY, dbo.fnCastAsDate(StartDate), dbo.fnCastAsDate(CloseDate))
			
        ,m.PropertyId, m.ListingStatus, m.AuctionDate, m.TsNumber,   m.Trustee, m.Site, 
                m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, m.MaxBid, 
               m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, m.DateRecorded,
               p.LotSize,  p.LandUse, 
                 p.AssessorDesc, m.Phase,
               Client,  EscrowCompany, MiscNotes, PrelistStatus,   Source,
                InvestorId, case when isnull(FeePercent, '') = '' then '20' else FeePercent end as FeePercent
                
			,cast(NetProfit as int) as NetProfit2
			,Bedrooms
			,Bathrooms
			,ConstructYear
			,GarageSqft
			,[Pool]
			,TotalSqft,
			m.DeedRestrictions, m.RehabMiscCosts + m.RehabEstimate AS CurrentRehabCosts, 
			m.NetSheet_SellingPrice AS ActualSale, m.NetSheet_SellingPrice - ISNULL(CONVERT(money, m.EstimatedSale), 0) AS ActualvsEstimate,
			m.[Type]
        FROM (SELECT * 
              FROM LvReo_MasterList 
              WHERE Apn LIKE '%'+@Apn+'%'
              AND isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%'
              AND isnull(ZipCode, '') LIKE '%'+@Zip+'%')  AS m          
         LEFT OUTER JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
         left join UserAccounts ua on m.InvestorId = ua.UserId
        where
			case
				when @showAllUnassigned = 0 then 1
				when (@showAllUnassigned = 1) and (ListingStatus = 'Unassigned') and (isnull(CloseDate, '') = '') then 1
				else 0
			end = 1
        ORDER BY
			case when ISNULL(CloseDate, '') = '' then m.ZipCode else 'z' end

END
GO
ALTER PROCEDURE [dbo].[updateLvreo_MasterListEx]
(
    @PropertyId        int, --Id
    @Apn               varchar(50)='', --Apn Key 
    @PropertyLocation  varchar(400)='', --Prop Location  
    @ZipCode           varchar(50)='', --ZipCode 
    @Stories           varchar(50)='', --Stories 
    @ConstructYear     varchar(50)='', --Const Year 
    @TotalSQft         varchar(50)='', --Total SQft 
    @Bedrooms          varchar(50)='', -- Bed 
    @Bathrooms         varchar(50)='', -- Bath 
    @GarageSqft        varchar(50)='', --GarageSqft 
    @Pool              varchar(50)='', --Pool 
    @Spa               varchar(50)='', --Spa 
	@CloseDate         varchar(50)='',
	@PurchasePrice     varchar(50)='',
	@RehabCosts        varchar(50)=''
	,@investorName		varchar(50)=''
	,@EMD varchar(50)=''
	,@Reimb varchar(50)=''
	,@Insur varchar(50)=''
	,@DateAccept varchar(50)=''
	,@StartDate varchar(50)=''
	,@EstimatedSale varchar(50)=''
	,@Assmts varchar(50)=''
	,@FeePercent varchar(50)=''
	,@netProfit2 int
	,@DeedRestrictions varchar(200)=''
	,@Type varchar(100)
)

AS

BEGIN
        UPDATE LvReo_ParcellAppraisal
        SET Stories = @Stories,
            ConstructYear = @ConstructYear,
            TotalSqft = @TotalSQft,
            Bedrooms = @Bedrooms,
            Bathrooms = @Bathrooms,
            GarageSqft = @GarageSqft,
            Pool = @Pool,
            Spa = @Spa,
            Apn = @Apn
        WHERE Apn = @Apn
        
        if @@ROWCOUNT = 0
			insert into LvReo_ParcellAppraisal(Stories, ConstructYear, TotalSqft, Bedrooms, Bathrooms,
				GarageSqft, Pool, Spa, Apn)
			values(@Stories, @ConstructYear, @TotalSqft, @Bedrooms, @Bathrooms,
				@GarageSqft, @Pool, @Spa, @Apn)

        UPDATE LvReo_MasterList
        SET 
            PropertyLocation = @PropertyLocation,
            ZipCode = @ZipCode,
			CloseDate = @CloseDate,
			PurchasePrice = @PurchasePrice,
			RehabCosts = @RehabCosts,
			Apn = @Apn,
			InvestorId = @investorName
			,EMD = @EMD
			,Reimb = @Reimb
			,Insur = @Insur
			,DateAccept = @DateAccept
			,StartDate = @StartDate
			,EstimatedSale = @EstimatedSale
			,Assmts = @Assmts
			,FeePercent = case when @FeePercent = '20' then null else @FeePercent end
			,NetProfit = @netProfit2
			,DeedRestrictions = @DeedRestrictions
			,[Type] = @Type
        WHERE PropertyId = @PropertyId
END
GO